package xxdsqljobDB

import com.github.drinkjava2.jdialects.Dialect
import mianThread.XxsqlJobThread

import java.text.SimpleDateFormat

/**
 * Created by Administrator on 2019-07-02.
 */
class XxdsqljobUtil {

    static getSqlTime() {
        return new java.sql.Timestamp(new Date().getTime())
    }

    static getSqlTime(Date date) {
        return new java.sql.Timestamp(date.getTime())
    }

    static getSqlTime(long date) {
        return new java.sql.Timestamp(long)
    }

    static getUUID() {
        return java.util.UUID.randomUUID()
    }

    static updateJobCfgRUN(long threadId, Date startTime) {
        try {
            def db = DBUtil.getGroovySqlInstance()
            def dateValue = new java.sql.Timestamp(startTime.getTime())
            db.withTransaction {
                db.execute("update JOB_CFG set  LAST_TIME = NULL ,START_TIME = ?,STATE = ? , state_info = NULL , LAST_UPDATED = ? where id = ?",
                        [dateValue, XxsqlJobThread.JOB_STATE_RUN, this.getSqlTime(), threadId])
            }
        } catch (Exception e) {
            println "updateJobCfgRUN ... 异常"
            e.printStackTrace()
        }
    }

    static updateJobCfgNORMAL(long threadId, Date LAST_TIME) {
        try {
            def db = DBUtil.getGroovySqlInstance()
            def dateValue = new java.sql.Timestamp(LAST_TIME.getTime())
            db.withTransaction {
                db.execute("update JOB_CFG set LAST_TIME = ?,STATE = ?  , state_info = NULL ,LAST_UPDATED = ? where id = ?",
                        [dateValue, XxsqlJobThread.JOB_STATE_NORMAL, this.getSqlTime(), threadId])
            }
        } catch (Exception e) {
            println "updateJobCfgNORMAL ... 异常"
            e.printStackTrace()
        }

    }

    static updateJobCfgERROR(long threadId, String STATE_INFO) {
        try {
            def sql = """
            update JOB_CFG set LAST_TIME = NULL ,STATE = '${XxsqlJobThread.JOB_STATE_ERROR}' ,STATE_INFO = ? ,LAST_UPDATED = ?
             where id = ${threadId}
            """
            def db = DBUtil.getGroovySqlInstance()
            db.withTransaction {
                db.execute(String.valueOf(sql),
                        [STATE_INFO, this.getSqlTime()])
            }
        } catch (Exception e) {
            println "updateJobCfgERROR ... 异常"
            e.printStackTrace()
        }
    }

    static insertJobHis(long threadId) {
        def sql = """
        INSERT INTO JOB_HIS
          (ID,
           JOB_ID,
           ACTIVE,
           CREATOR,
           INIT_TIME,
           JOB_NAME,
           LAST_TIME,
           PERIOD,
           START_TIME,
           STATE,
           STATE_INFO,
           UNIT,
           UPDATER,
           UPLOAD_PATH,
           last_Updated,
           log_Dated)
          SELECT '${this.getUUID()}',
                 ID,
                 ACTIVE,
                 CREATOR,
                 INIT_TIME,
                 JOB_NAME,
                 LAST_TIME,
                 PERIOD,
                 START_TIME,
                 STATE,
                 STATE_INFO,
                 UNIT,
                 UPDATER,
                 UPLOAD_PATH,
                 last_Updated,
                 ?
            FROM JOB_CFG
           WHERE id = ${threadId}
        """
        try {
            def db = DBUtil.getGroovySqlInstance()
            db.withTransaction {
                db.execute(String.valueOf(sql), [this.getSqlTime()])
            }
        } catch (Exception e) {
            e.printStackTrace()
        }
    }

    static updateJobCfgActive(long threadId, String active, String user) {
        try {
            def sql = """
            UPDATE JOB_CFG t
            SET t.ACTIVE = '${active}',
             t.UPDATER = '${user}',
             t.LAST_UPDATED = ?
             WHERE t.ID = ${threadId}
            """
            def db = DBUtil.getGroovySqlInstance()
            db.withTransaction {
                db.execute(String.valueOf(sql),
                        [this.getSqlTime()])
            }
        } catch (Exception e) {
            println "updateJobCfgERROR ... 异常"
            e.printStackTrace()
        }
    }

    static queryMenu(jobId, menuName) {
        def sql = """
        SELECT TA.ID RPT_ID,
        TA.TITLE,
        TB.JOB_ID,
        tb.SQL_IDS,
        tb.PARAM
        FROM CT_UI_MENU TA
        LEFT JOIN JOB_REPORT TB
            ON TA.ID = TB.RPT_ID
            AND TB.JOB_ID = ${jobId}
        where ta.title like '%${menuName ?: ""}%'
        ORDER BY TB.JOB_ID
        """
        def db = DBUtil.getGroovySqlInstance()
        return db.rows(String.valueOf(sql))
    }

    static queryUser(jobId, user) {
        def sql = """
        SELECT TA.ID USER_ID,
               TA.TITLE,
               TA.USER_NAME,
               TA.EMAIL,
               TB.JOB_ID
          FROM CT_SEC_USER TA
          LEFT JOIN JOB_EMAIL_USER TB
            ON TA.ID = TB.USER_ID
           AND TB.JOB_ID = ${jobId}
         WHERE TA.TITLE LIKE '%${user ?: ""}%' or ta.user_name like '%${user ?: ""}%'
         ORDER BY TB.JOB_ID
        """
        def db = DBUtil.getGroovySqlInstance()
        return db.rows(String.valueOf(sql))
    }

    static queryReport(jobId) {
        def sql = """
            SELECT TC.TITLE,
               TA.JOB_NAME,
               TB.SQL_IDS,
               tb.PARAM,
               ta.UPLOAD_PATH
          FROM JOB_CFG TA
         INNER JOIN JOB_REPORT TB
            ON TA.ID = TB.JOB_ID
         INNER JOIN CT_UI_MENU TC
            ON TC.ID = TB.RPT_ID
         WHERE TA.ACTIVE = 'Y'
           AND TA.ID = ${jobId}
        """
        def db = DBUtil.getGroovySqlInstance()
        return db.rows(String.valueOf(sql))
    }

    static getSql(sqlId) {

        def sql = "SELECT CONTEXT FROM RT_TEM_CONT  WHERE NAME = '${sqlId}' ".toString()
        def db = DBUtil.getGroovySqlInstance()
        def conn = db.getConnection() ?: db.getDataSource().getConnection()
        def pst = conn.createStatement()
        def result = pst.executeQuery(sql)
        if (result.next()) {
            result = result.toRowResult()
        }
        def sqlLast = DBUtil.Clob2String(result?.get("CONTEXT"))
        conn.close()
        pst.close()

        return sqlLast
    }

    static int getTotal(sql) {
        def db = DBUtil.getGroovySqlInstance()
        def sqlNew = String.valueOf("select count(1) TOTAL from (${String.valueOf(sql)}) aa")
        //println sqlNew
        return db.firstRow(sqlNew).get("TOTAL")
    }

    static getPageData(sql, int offset, int max) {
        def db = DBUtil.getGroovySqlInstance()
        def dsn = db.getConnection() ?: db.getDataSource().getConnection()
        Dialect dialect = Dialect.guessDialect(dsn)
        sql = dialect.pagin(offset, max, String.valueOf(sql))
        dialect = null
        //println sql
        return db.rows(sql)
    }

    static addFileMapping(String fileName, String physicalName) {
        def addId = this.getUUID()
        def sql = """
        INSERT INTO job_report_file(id,file_name,physical_name,date_created)
        values('${addId}',?,?,?)
        """
        def db = DBUtil.getGroovySqlInstance()
        db.withTransaction {
            db.executeInsert(String.valueOf(sql), [fileName, physicalName, this.getSqlTime()])
        }
        return new UpLoadFile(fileName: fileName, physicalName: physicalName)
    }

    static sendEmail(long threadId, List<UpLoadFile> upLoadFiles) {
        def reports = this.queryReport(threadId)

        def users = this.queryUser(threadId, null)

        try {
            if (users) {
                SimpleDateFormat sdf = new SimpleDateFormat('M月dd日')
                def JOB_DATE =sdf.format(new Date().clearTime())

                def JOB_NAME = reports."JOB_NAME".get(0)
                def UPLOAD_PATH = reports."UPLOAD_PATH".get(0)
                def mails = []
                users.each {
                    if (!mails.contains(it.get("JOB_ID")) && it.get("JOB_ID")) {
                        mails.add(it.get("EMAIL"))
                    }
                }
                println "${JOB_NAME}"
                println "${UPLOAD_PATH}"
                println "${JOB_DATE}"
             /*   def JOB_DATE=getSqlTime(new Date().getTime());*/
                def subject = MailUtil.getProperty().getProperty("mail.smtp.send.subject")
                subject = BeetlUtil.getStrTemplate([JOB_DATE: JOB_DATE],subject)
                def templete = MailUtil.getProperty().getProperty("mail.smtp.send.templete")
                templete = BeetlUtil.getStrTemplate([JOB_NAME: JOB_NAME, JOB_DATE: JOB_DATE], templete)
                MailUtil.sendMail(subject, mails, templete, upLoadFiles)
            }
        } catch (Exception e) {
            throw new Exception("Email Error!${e.getMessage()}")
        }
    }
}

/**
 * 新增文件路径类，不入侵domain
 */
class UpLoadFile {
    String fileName
    String physicalName
}